USE [DSRSystem]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetFinalizedIndicatorsByCompanyIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetFinalizedIndicatorsByCompanyIds]
GO

CREATE PROCEDURE [dbo].[spGetFinalizedIndicatorsByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)
	

		DECLARE @transaction_isolation_level SMALLINT;
	
	SELECT @transaction_isolation_level = transaction_isolation_level
    FROM sys.dm_exec_requests
    WHERE session_id = @@spid;
	
	print('spGetFinalizedIndicatorsByCompanyIds transaction_isolation_level: ' + STR(@transaction_isolation_level))

	select i.IndicatorId as Id,
		   ic.CompanyId,
		   i.Name,
	       i.Number,
		   i.Description,
		   ac.Score,
		   dbo.fnGetGovernanceIndicatorOutlook(i.IndicatorId) as Outlook,
		   dbo.fnGetGovernanceIndicatorTickedboxes(i.IndicatorId) as Tickedboxes,
		   dbo.fnGetGovernanceIndicatorComments(i.IndicatorId) as Comments,
	--	dbo.fnGetGovernanceIndicatorCommentByPosition(i.IndicatorId, 2) SC
		   dbo.fnGetGovernanceIndicatorSources(i.IndicatorId) as Sources
	from Companies c 
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = c.CompanyId
	join IndicatorsInCompanies ic on c.CompanyId = ic.CompanyId
	join Indicators i on ic.IndicatorId = i.IndicatorId and i.Version = c.WorkingVersion - 1
	join AnswerCategories ac on i.RatedSelectedAnswer = ac.AnswerCategoryId
	where c.IsParked = 0 
	order by dbo.fFormatIndicatorNumber(i.Number)

END
GO


